﻿/* 
 * Since 2008 - 2013
 */

package com.hh.bss.oa.dao.impl;

import java.util.*;

import com.hh.bss.base.*;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.oa.dao.*;
import com.hh.bss.oa.entity.*;
import com.hh.bss.oa.service.*;
import com.hh.bss.oa.vo.query.*;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class OaMeetingRoomLogDaoImpl extends BaseHibernateDao<OaMeetingRoomLog,java.lang.Integer>  implements OaMeetingRoomLogDao<OaMeetingRoomLog,java.lang.Integer>{

	public Class getEntityClass() {
		return OaMeetingRoomLog.class;
	}
	
	public Page findPage(OaMeetingRoomLogQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.room_id roomId,t1.name roomName,t1.branchCompanyId,t1.branchCompanyName,a.title,t1.address,a.use_type useType,a.meeting_date meetingDate,DATE_FORMAT(a.meeting_date,'%Y-%m-%d') meetingDateString,a.begin_dt beginDt,DATE_FORMAT(a.begin_dt,'%H:%i') beginDtString,a.end_dt endDt,DATE_FORMAT(a.end_dt,'%H:%i') endDtString,a.order_account orderAccount,b.create_dt orderDt,a.status,a.create_dt createDt from  oa_meeting_room_log a ");
		//关联会议室
		sql.append(" left join ( ");
		sql.append(" select a.id,a.name,a.type,a.branch_company_id branchCompanyId,d.name branchCompanyName,a.address,a.size,a.meetings,cc.meetingsname,a.comment,a.status,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt from  oa_meeting_room a ");
		sql.append(" left join  (select aa.id,group_concat((bb.name) separator ',') meetingsname from (select a.id,a.meetings from oa_meeting_room a where a.type=2) aa,(select b.id,b.name from oa_meeting_room b where b.type=1 ) bb where FIND_IN_SET(bb.id,aa.meetings)>0 group by aa.id) cc on a.id=cc.id ");			
		sql.append(" left join  system_branch_company d  on a.branch_company_id=d.id ");
		sql.append(" ) t1 on  a.room_id=t1.id ");
		sql.append(" left join oa_meeting_room_taking b on a.task_id=b.id ");
		sql.append(" ) t ");
		List values = new ArrayList();
		
		sql.append(" where 1=1 ");
		
		//values.add(query.getProjectId());
		   if(isNotEmpty(query.getBranchCompanyId())) {
	            sql.append(" and  t.branchCompanyId = ?");
	            values.add(query.getBranchCompanyId());
	        }
		   if(isNotEmpty(query.getRoomId())) {
	            sql.append(" and  t.roomId = ?");
	            values.add(query.getRoomId());
	        }
		   if(isNotEmpty(query.getMeetingName())) {
	            sql.append(" and  t.name like ? ");
	            
	            values.add("%"+query.getMeetingName()+"%");

	        }
		   if(isNotEmpty(query.getTitle())) {
	            sql.append(" and  t.title like ? ");
	            
	            values.add("%"+query.getTitle()+"%");

	        }
		   if(isNotEmpty(query.getOrderAccount())) {
			    sql.append(" and  t.orderAccount = ? ");	            
	            values.add(query.getOrderAccount());
	        }
		   if(isNotEmpty(query.getMeetingDate())) {
			    sql.append(" and  t.meetingDate = ? ");
	            
	            values.add(query.getMeetingDate());
	        }
	          if(isNotEmpty(query.getStatus())) {
		            sql.append(" and  t.status = ? ");
		            values.add(query.getStatus());
		        }
	          if(isNotEmpty(query.getUseType())) {
		            sql.append(" and  t.useType = ? ");
		            values.add(query.getUseType());
		        }  
	     sql.append(" order by t.meetingDate desc,t.beginDtString desc ");
		
		//return super.getJdbcDao().createPage(query, values, mapper, pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber()); 
	}

	@Override
	public List findList(OaMeetingRoomLogQuery query) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.room_id roomId,t1.name roomName,t1.branchCompanyId,t1.branchCompanyName,b.title,t1.address,b.use_type useType,a.meeting_date meetingDate,DATE_FORMAT(a.meeting_date,'%Y-%m-%d') meetingDateString,a.begin_dt beginDt,DATE_FORMAT(a.begin_dt,'%H:%i') beginDtString,a.end_dt endDt,DATE_FORMAT(a.end_dt,'%H:%i') endDtString,b.order_account orderAccount,b.create_dt orderDt,a.status,a.create_dt createDt from  oa_meeting_room_log a ");
		//关联会议室
		sql.append(" left join ( ");
		sql.append(" select a.id,a.name,a.type,a.branch_company_id branchCompanyId,d.name branchCompanyName,a.address,a.size,a.meetings,cc.meetingsname,a.comment,a.status,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt from  oa_meeting_room a ");
		sql.append(" left join  (select aa.id,group_concat((bb.name) separator ',') meetingsname from (select a.id,a.meetings from oa_meeting_room a where a.type=2) aa,(select b.id,b.name from oa_meeting_room b where b.type=1 ) bb where FIND_IN_SET(bb.id,aa.meetings)>0 group by aa.id) cc on a.id=cc.id ");			
		sql.append(" left join  system_branch_company d  on a.branch_company_id=d.id ");
		sql.append(" ) t1 on  a.room_id=t1.id ");
		sql.append(" left join oa_meeting_room_taking b on a.task_id=b.id ");
		sql.append(" ) t ");
		List values = new ArrayList();
		
		sql.append(" where 1=1 ");
		
		//values.add(query.getProjectId());
		   if(isNotEmpty(query.getBranchCompanyId())) {
	            sql.append(" and  t.branchCompanyId = ?");
	            values.add(query.getBranchCompanyId());
	        }
		   if(isNotEmpty(query.getRoomId())) {
	            sql.append(" and  t.roomId = ?");
	            values.add(query.getRoomId());
	        }
		   if(isNotEmpty(query.getMeetingName())) {
	            sql.append(" and  t.name like ? ");
	            
	            values.add("%"+query.getMeetingName()+"%");

	        }
		   if(isNotEmpty(query.getTitle())) {
	            sql.append(" and  t.title like ? ");
	            
	            values.add("%"+query.getTitle()+"%");

	        }
		   if(isNotEmpty(query.getOrderAccount())) {
			    sql.append(" and  t.orderAccount = ? ");	            
	            values.add(query.getOrderAccount());
	        }
		   if(isNotEmpty(query.getMeetingDate())) {
			    sql.append(" and  t.meetingDate = ? ");
	            
	            values.add(query.getMeetingDate());
	        }
	          if(isNotEmpty(query.getStatus())) {
		            sql.append(" and  t.status = ? ");
		            values.add(query.getStatus());
		        }
	          if(isNotEmpty(query.getUseType())) {
		            sql.append(" and  t.useType = ? ");
		            values.add(query.getUseType());
		        }  
	     sql.append(" order by t.meetingDate desc,t.beginDtString desc ");
		
		//return super.getJdbcDao().createPage(query, values, mapper, pageMaxSize, pageNumber)
	    return super.getJdbcDao().findByQuery(sql.toString(),values.toArray(), new MapObjectRowMapper());
		 

	}
	

}
